{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "aa5b77e5-5c35-485c-a999-06ece846d950",
   "metadata": {},
   "source": [
    "# 第三节、数据的输入与输出"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9af3c182-adc6-47f8-a610-baf711462d3e",
   "metadata": {},
   "source": [
    "## （1）导入导出CSV数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "4ad9ac1b-e40b-479d-a734-74381aab581d",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "e06926f6-4d0e-4233-88fd-fdf61eb07cd7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>IT</th>\n",
       "      <th>化工</th>\n",
       "      <th>生物</th>\n",
       "      <th>教师</th>\n",
       "      <th>销售</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>114</td>\n",
       "      <td>41</td>\n",
       "      <td>80</td>\n",
       "      <td>100</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>139</td>\n",
       "      <td>138</td>\n",
       "      <td>144</td>\n",
       "      <td>9</td>\n",
       "      <td>92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>125</td>\n",
       "      <td>142</td>\n",
       "      <td>128</td>\n",
       "      <td>106</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>63</td>\n",
       "      <td>80</td>\n",
       "      <td>51</td>\n",
       "      <td>6</td>\n",
       "      <td>125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>28</td>\n",
       "      <td>14</td>\n",
       "      <td>119</td>\n",
       "      <td>92</td>\n",
       "      <td>116</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    IT   化工   生物   教师   销售\n",
       "0  114   41   80  100    5\n",
       "1  139  138  144    9   92\n",
       "2  125  142  128  106   30\n",
       "3   63   80   51    6  125\n",
       "4   28   14  119   92  116"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame(\n",
    "    data=np.random.randint(0, 150, size=(50, 5)),  # 薪资情况\n",
    "    columns=['IT','化工','生物','教师','销售'],\n",
    ")\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "2be8e9bf-6f6c-426b-a4cf-bee9efa1f0f8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(50, 5)"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape  # 复习一下之前的知识，shape查看形状"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "44bcc30e-aef1-4c29-ba16-43ca15a7ae7d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 50 entries, 0 to 49\n",
      "Data columns (total 5 columns):\n",
      " #   Column  Non-Null Count  Dtype\n",
      "---  ------  --------------  -----\n",
      " 0   IT      50 non-null     int32\n",
      " 1   化工      50 non-null     int32\n",
      " 2   生物      50 non-null     int32\n",
      " 3   教师      50 non-null     int32\n",
      " 4   销售      50 non-null     int32\n",
      "dtypes: int32(5)\n",
      "memory usage: 1.1 KB\n"
     ]
    }
   ],
   "source": [
    "df.info()   # info()查看dataframe的概述信息"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "98f8c0f3-f032-4ff0-a5b3-57fb3ae0ef9c",
   "metadata": {},
   "source": [
    "### 导出"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "af40df53-e9d9-4a89-8bcc-dc92f1bceccc",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv('salary1.csv')   # 默认是将行索引也存入csv中，默认将列索引也存入"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "8e030ead-387f-45e4-9879-9b680cf03293",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 指定False就不会导出列索引header，和行索引index_col\n",
    "df.to_csv('salary2.csv', sep=',', header=False, index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d86cc35c-db49-402b-af02-c82949766a0f",
   "metadata": {},
   "source": [
    "## 导入"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "583d89f0-1cba-4544-9d60-916fe1f95e9e",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>IT</th>\n",
       "      <th>化工</th>\n",
       "      <th>生物</th>\n",
       "      <th>教师</th>\n",
       "      <th>销售</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>114</td>\n",
       "      <td>41</td>\n",
       "      <td>80</td>\n",
       "      <td>100</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>139</td>\n",
       "      <td>138</td>\n",
       "      <td>144</td>\n",
       "      <td>9</td>\n",
       "      <td>92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>125</td>\n",
       "      <td>142</td>\n",
       "      <td>128</td>\n",
       "      <td>106</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>63</td>\n",
       "      <td>80</td>\n",
       "      <td>51</td>\n",
       "      <td>6</td>\n",
       "      <td>125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>28</td>\n",
       "      <td>14</td>\n",
       "      <td>119</td>\n",
       "      <td>92</td>\n",
       "      <td>116</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>53</td>\n",
       "      <td>54</td>\n",
       "      <td>54</td>\n",
       "      <td>74</td>\n",
       "      <td>86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>72</td>\n",
       "      <td>63</td>\n",
       "      <td>96</td>\n",
       "      <td>23</td>\n",
       "      <td>132</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>135</td>\n",
       "      <td>34</td>\n",
       "      <td>1</td>\n",
       "      <td>67</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>114</td>\n",
       "      <td>77</td>\n",
       "      <td>33</td>\n",
       "      <td>82</td>\n",
       "      <td>149</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>106</td>\n",
       "      <td>58</td>\n",
       "      <td>129</td>\n",
       "      <td>70</td>\n",
       "      <td>62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>16</td>\n",
       "      <td>14</td>\n",
       "      <td>107</td>\n",
       "      <td>105</td>\n",
       "      <td>72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>54</td>\n",
       "      <td>19</td>\n",
       "      <td>44</td>\n",
       "      <td>57</td>\n",
       "      <td>95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>30</td>\n",
       "      <td>125</td>\n",
       "      <td>103</td>\n",
       "      <td>146</td>\n",
       "      <td>105</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "      <td>63</td>\n",
       "      <td>80</td>\n",
       "      <td>133</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>138</td>\n",
       "      <td>110</td>\n",
       "      <td>96</td>\n",
       "      <td>24</td>\n",
       "      <td>149</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>1</td>\n",
       "      <td>94</td>\n",
       "      <td>21</td>\n",
       "      <td>123</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>54</td>\n",
       "      <td>94</td>\n",
       "      <td>137</td>\n",
       "      <td>23</td>\n",
       "      <td>78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>35</td>\n",
       "      <td>102</td>\n",
       "      <td>11</td>\n",
       "      <td>105</td>\n",
       "      <td>120</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>54</td>\n",
       "      <td>133</td>\n",
       "      <td>104</td>\n",
       "      <td>58</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>110</td>\n",
       "      <td>16</td>\n",
       "      <td>26</td>\n",
       "      <td>120</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>94</td>\n",
       "      <td>10</td>\n",
       "      <td>58</td>\n",
       "      <td>134</td>\n",
       "      <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>60</td>\n",
       "      <td>87</td>\n",
       "      <td>39</td>\n",
       "      <td>61</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>114</td>\n",
       "      <td>5</td>\n",
       "      <td>54</td>\n",
       "      <td>13</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>42</td>\n",
       "      <td>120</td>\n",
       "      <td>73</td>\n",
       "      <td>25</td>\n",
       "      <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>64</td>\n",
       "      <td>100</td>\n",
       "      <td>105</td>\n",
       "      <td>23</td>\n",
       "      <td>106</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>12</td>\n",
       "      <td>39</td>\n",
       "      <td>50</td>\n",
       "      <td>137</td>\n",
       "      <td>86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>75</td>\n",
       "      <td>54</td>\n",
       "      <td>116</td>\n",
       "      <td>87</td>\n",
       "      <td>67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>57</td>\n",
       "      <td>99</td>\n",
       "      <td>131</td>\n",
       "      <td>52</td>\n",
       "      <td>74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>74</td>\n",
       "      <td>108</td>\n",
       "      <td>98</td>\n",
       "      <td>130</td>\n",
       "      <td>39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>104</td>\n",
       "      <td>106</td>\n",
       "      <td>29</td>\n",
       "      <td>123</td>\n",
       "      <td>142</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>103</td>\n",
       "      <td>80</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>132</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>94</td>\n",
       "      <td>2</td>\n",
       "      <td>129</td>\n",
       "      <td>101</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>46</td>\n",
       "      <td>49</td>\n",
       "      <td>36</td>\n",
       "      <td>134</td>\n",
       "      <td>132</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>65</td>\n",
       "      <td>93</td>\n",
       "      <td>101</td>\n",
       "      <td>46</td>\n",
       "      <td>69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>15</td>\n",
       "      <td>144</td>\n",
       "      <td>27</td>\n",
       "      <td>55</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>87</td>\n",
       "      <td>116</td>\n",
       "      <td>64</td>\n",
       "      <td>78</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>124</td>\n",
       "      <td>20</td>\n",
       "      <td>76</td>\n",
       "      <td>57</td>\n",
       "      <td>107</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>90</td>\n",
       "      <td>147</td>\n",
       "      <td>128</td>\n",
       "      <td>101</td>\n",
       "      <td>118</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>101</td>\n",
       "      <td>116</td>\n",
       "      <td>30</td>\n",
       "      <td>39</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>4</td>\n",
       "      <td>73</td>\n",
       "      <td>1</td>\n",
       "      <td>50</td>\n",
       "      <td>104</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>10</td>\n",
       "      <td>68</td>\n",
       "      <td>118</td>\n",
       "      <td>77</td>\n",
       "      <td>44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>148</td>\n",
       "      <td>88</td>\n",
       "      <td>100</td>\n",
       "      <td>139</td>\n",
       "      <td>108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>71</td>\n",
       "      <td>132</td>\n",
       "      <td>149</td>\n",
       "      <td>10</td>\n",
       "      <td>113</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>40</td>\n",
       "      <td>115</td>\n",
       "      <td>111</td>\n",
       "      <td>40</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>95</td>\n",
       "      <td>136</td>\n",
       "      <td>126</td>\n",
       "      <td>29</td>\n",
       "      <td>57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>49</td>\n",
       "      <td>84</td>\n",
       "      <td>116</td>\n",
       "      <td>94</td>\n",
       "      <td>92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>114</td>\n",
       "      <td>89</td>\n",
       "      <td>135</td>\n",
       "      <td>97</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>30</td>\n",
       "      <td>139</td>\n",
       "      <td>80</td>\n",
       "      <td>25</td>\n",
       "      <td>141</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>61</td>\n",
       "      <td>15</td>\n",
       "      <td>74</td>\n",
       "      <td>1</td>\n",
       "      <td>145</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>4</td>\n",
       "      <td>101</td>\n",
       "      <td>48</td>\n",
       "      <td>46</td>\n",
       "      <td>44</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     IT   化工   生物   教师   销售\n",
       "0   114   41   80  100    5\n",
       "1   139  138  144    9   92\n",
       "2   125  142  128  106   30\n",
       "3    63   80   51    6  125\n",
       "4    28   14  119   92  116\n",
       "5    53   54   54   74   86\n",
       "6    72   63   96   23  132\n",
       "7   135   34    1   67   45\n",
       "8   114   77   33   82  149\n",
       "9   106   58  129   70   62\n",
       "10   16   14  107  105   72\n",
       "11   54   19   44   57   95\n",
       "12   30  125  103  146  105\n",
       "13    7    8   63   80  133\n",
       "14  138  110   96   24  149\n",
       "15    1   94   21  123   29\n",
       "16   54   94  137   23   78\n",
       "17   35  102   11  105  120\n",
       "18   54  133  104   58   17\n",
       "19  110   16   26  120   50\n",
       "20   94   10   58  134   47\n",
       "21   60   87   39   61   22\n",
       "22  114    5   54   13   32\n",
       "23   42  120   73   25   48\n",
       "24   64  100  105   23  106\n",
       "25   12   39   50  137   86\n",
       "26   75   54  116   87   67\n",
       "27   57   99  131   52   74\n",
       "28   74  108   98  130   39\n",
       "29  104  106   29  123  142\n",
       "30  103   80    4    3  132\n",
       "31   94    2  129  101   22\n",
       "32   46   49   36  134  132\n",
       "33   65   93  101   46   69\n",
       "34   15  144   27   55    9\n",
       "35   87  116   64   78   13\n",
       "36  124   20   76   57  107\n",
       "37   90  147  128  101  118\n",
       "38  101  116   30   39   21\n",
       "39    4   73    1   50  104\n",
       "40   10   68  118   77   44\n",
       "41  148   88  100  139  108\n",
       "42   71  132  149   10  113\n",
       "43   40  115  111   40   56\n",
       "44   95  136  126   29   57\n",
       "45   49   84  116   94   92\n",
       "46  114   89  135   97   19\n",
       "47   30  139   80   25  141\n",
       "48   61   15   74    1  145\n",
       "49    4  101   48   46   44"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 第一个参数是读哪个文件\n",
    "# sep参数是间隔符\n",
    "# header是以哪一列为行索引\n",
    "# index_col是以哪一行为列索引\n",
    "df2 = pd.read_csv('salary1.csv', sep=',', header=[0], index_col=[0])\n",
    "df2"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6b15bdae-0dba-481e-9e87-5f610c17647e",
   "metadata": {},
   "source": [
    "## 导入导出Excel数据"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "419f3597-36c1-4964-87bc-147a1569bf7d",
   "metadata": {},
   "source": [
    "Pandas要操作Excel必须安装两个Python的包：\n",
    "- `pip install xlrd`\n",
    "- `pip install xlwt`\n",
    "\n",
    "或者直接安装`pip install openpyxl`"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a8a8daaa-b37f-4b4c-990e-865390e77f47",
   "metadata": {},
   "source": [
    "### （1）导出"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "6440ee5b-5f6b-438a-90c4-fd462f7af9cd",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>IT</th>\n",
       "      <th>化工</th>\n",
       "      <th>生物</th>\n",
       "      <th>教师</th>\n",
       "      <th>销售</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>30</td>\n",
       "      <td>22</td>\n",
       "      <td>13</td>\n",
       "      <td>43</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>9</td>\n",
       "      <td>42</td>\n",
       "      <td>40</td>\n",
       "      <td>13</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>11</td>\n",
       "      <td>38</td>\n",
       "      <td>0</td>\n",
       "      <td>19</td>\n",
       "      <td>46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>11</td>\n",
       "      <td>30</td>\n",
       "      <td>48</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>33</td>\n",
       "      <td>37</td>\n",
       "      <td>35</td>\n",
       "      <td>40</td>\n",
       "      <td>36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>7</td>\n",
       "      <td>23</td>\n",
       "      <td>33</td>\n",
       "      <td>23</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>15</td>\n",
       "      <td>16</td>\n",
       "      <td>33</td>\n",
       "      <td>1</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>46</td>\n",
       "      <td>15</td>\n",
       "      <td>5</td>\n",
       "      <td>12</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>25</td>\n",
       "      <td>10</td>\n",
       "      <td>30</td>\n",
       "      <td>7</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>44</td>\n",
       "      <td>36</td>\n",
       "      <td>38</td>\n",
       "      <td>8</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>22</td>\n",
       "      <td>30</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>44</td>\n",
       "      <td>9</td>\n",
       "      <td>41</td>\n",
       "      <td>45</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2</td>\n",
       "      <td>19</td>\n",
       "      <td>48</td>\n",
       "      <td>15</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>45</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>9</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>34</td>\n",
       "      <td>13</td>\n",
       "      <td>32</td>\n",
       "      <td>41</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>48</td>\n",
       "      <td>35</td>\n",
       "      <td>23</td>\n",
       "      <td>35</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>32</td>\n",
       "      <td>29</td>\n",
       "      <td>21</td>\n",
       "      <td>34</td>\n",
       "      <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>6</td>\n",
       "      <td>47</td>\n",
       "      <td>46</td>\n",
       "      <td>43</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>11</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>21</td>\n",
       "      <td>36</td>\n",
       "      <td>45</td>\n",
       "      <td>19</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>45</td>\n",
       "      <td>40</td>\n",
       "      <td>5</td>\n",
       "      <td>35</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>0</td>\n",
       "      <td>40</td>\n",
       "      <td>41</td>\n",
       "      <td>36</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>42</td>\n",
       "      <td>30</td>\n",
       "      <td>2</td>\n",
       "      <td>31</td>\n",
       "      <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>6</td>\n",
       "      <td>37</td>\n",
       "      <td>19</td>\n",
       "      <td>42</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>27</td>\n",
       "      <td>6</td>\n",
       "      <td>12</td>\n",
       "      <td>40</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>17</td>\n",
       "      <td>42</td>\n",
       "      <td>4</td>\n",
       "      <td>15</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>31</td>\n",
       "      <td>49</td>\n",
       "      <td>23</td>\n",
       "      <td>15</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>15</td>\n",
       "      <td>13</td>\n",
       "      <td>18</td>\n",
       "      <td>40</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>32</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>25</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>26</td>\n",
       "      <td>10</td>\n",
       "      <td>25</td>\n",
       "      <td>43</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>10</td>\n",
       "      <td>5</td>\n",
       "      <td>8</td>\n",
       "      <td>44</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>12</td>\n",
       "      <td>27</td>\n",
       "      <td>46</td>\n",
       "      <td>29</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>18</td>\n",
       "      <td>0</td>\n",
       "      <td>41</td>\n",
       "      <td>34</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>37</td>\n",
       "      <td>21</td>\n",
       "      <td>22</td>\n",
       "      <td>8</td>\n",
       "      <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>29</td>\n",
       "      <td>5</td>\n",
       "      <td>37</td>\n",
       "      <td>12</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>7</td>\n",
       "      <td>12</td>\n",
       "      <td>9</td>\n",
       "      <td>21</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>42</td>\n",
       "      <td>20</td>\n",
       "      <td>7</td>\n",
       "      <td>33</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>10</td>\n",
       "      <td>41</td>\n",
       "      <td>19</td>\n",
       "      <td>3</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>16</td>\n",
       "      <td>19</td>\n",
       "      <td>49</td>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>8</td>\n",
       "      <td>33</td>\n",
       "      <td>8</td>\n",
       "      <td>23</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>16</td>\n",
       "      <td>49</td>\n",
       "      <td>17</td>\n",
       "      <td>44</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>12</td>\n",
       "      <td>15</td>\n",
       "      <td>23</td>\n",
       "      <td>33</td>\n",
       "      <td>39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>22</td>\n",
       "      <td>47</td>\n",
       "      <td>10</td>\n",
       "      <td>44</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>5</td>\n",
       "      <td>21</td>\n",
       "      <td>39</td>\n",
       "      <td>47</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>14</td>\n",
       "      <td>5</td>\n",
       "      <td>15</td>\n",
       "      <td>39</td>\n",
       "      <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>1</td>\n",
       "      <td>23</td>\n",
       "      <td>24</td>\n",
       "      <td>11</td>\n",
       "      <td>46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>1</td>\n",
       "      <td>44</td>\n",
       "      <td>32</td>\n",
       "      <td>47</td>\n",
       "      <td>44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>29</td>\n",
       "      <td>19</td>\n",
       "      <td>0</td>\n",
       "      <td>17</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>16</td>\n",
       "      <td>2</td>\n",
       "      <td>25</td>\n",
       "      <td>43</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>43</td>\n",
       "      <td>5</td>\n",
       "      <td>25</td>\n",
       "      <td>13</td>\n",
       "      <td>36</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    IT  化工  生物  教师  销售\n",
       "0   30  22  13  43  10\n",
       "1    9  42  40  13  28\n",
       "2   11  38   0  19  46\n",
       "3    2  11  30  48  19\n",
       "4   33  37  35  40  36\n",
       "5    7  23  33  23   5\n",
       "6   15  16  33   1  24\n",
       "7   46  15   5  12   5\n",
       "8   25  10  30   7   9\n",
       "9   44  36  38   8  25\n",
       "10  22  30   9   9   7\n",
       "11  44   9  41  45  10\n",
       "12   2  19  48  15  19\n",
       "13  45   5   4   9  30\n",
       "14  34  13  32  41   5\n",
       "15  48  35  23  35  15\n",
       "16  32  29  21  34  48\n",
       "17   6  47  46  43  32\n",
       "18  11   2   5   1  16\n",
       "19  21  36  45  19   9\n",
       "20  45  40   5  35   0\n",
       "21   0  40  41  36  18\n",
       "22  42  30   2  31  43\n",
       "23   6  37  19  42   4\n",
       "24  27   6  12  40  20\n",
       "25  17  42   4  15   0\n",
       "26  31  49  23  15  15\n",
       "27  15  13  18  40   4\n",
       "28  32   4   3  25  21\n",
       "29  26  10  25  43  27\n",
       "30  10   5   8  44   0\n",
       "31  12  27  46  29  28\n",
       "32  18   0  41  34  35\n",
       "33  37  21  22   8  47\n",
       "34  29   5  37  12  26\n",
       "35   7  12   9  21  29\n",
       "36  42  20   7  33  18\n",
       "37  10  41  19   3  17\n",
       "38  16  19  49   8   3\n",
       "39   8  33   8  23   2\n",
       "40  16  49  17  44  10\n",
       "41  12  15  23  33  39\n",
       "42  22  47  10  44  26\n",
       "43   5  21  39  47  15\n",
       "44  14   5  15  39  48\n",
       "45   1  23  24  11  46\n",
       "46   1  44  32  47  44\n",
       "47  29  19   0  17  11\n",
       "48  16   2  25  43  45\n",
       "49  43   5  25  13  36"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Python</th>\n",
       "      <th>Tensorflow</th>\n",
       "      <th>Keras</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>39.49</td>\n",
       "      <td>36.66</td>\n",
       "      <td>34.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>49.44</td>\n",
       "      <td>14.92</td>\n",
       "      <td>49.90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3.00</td>\n",
       "      <td>35.33</td>\n",
       "      <td>39.78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7.88</td>\n",
       "      <td>8.69</td>\n",
       "      <td>37.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>45.48</td>\n",
       "      <td>43.76</td>\n",
       "      <td>32.56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>145</th>\n",
       "      <td>11.39</td>\n",
       "      <td>10.22</td>\n",
       "      <td>11.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146</th>\n",
       "      <td>11.36</td>\n",
       "      <td>35.64</td>\n",
       "      <td>42.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>147</th>\n",
       "      <td>24.47</td>\n",
       "      <td>2.19</td>\n",
       "      <td>41.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>148</th>\n",
       "      <td>41.90</td>\n",
       "      <td>27.09</td>\n",
       "      <td>23.10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149</th>\n",
       "      <td>4.25</td>\n",
       "      <td>41.91</td>\n",
       "      <td>15.22</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>150 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     Python  Tensorflow  Keras\n",
       "0     39.49       36.66  34.22\n",
       "1     49.44       14.92  49.90\n",
       "2      3.00       35.33  39.78\n",
       "3      7.88        8.69  37.47\n",
       "4     45.48       43.76  32.56\n",
       "..      ...         ...    ...\n",
       "145   11.39       10.22  11.65\n",
       "146   11.36       35.64  42.00\n",
       "147   24.47        2.19  41.86\n",
       "148   41.90       27.09  23.10\n",
       "149    4.25       41.91  15.22\n",
       "\n",
       "[150 rows x 3 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# 先新建两个df\n",
    "df1 = pd.DataFrame(\n",
    "    data=np.random.randint(0, 50, size=(50, 5)),  # 薪资情况\n",
    "    columns=['IT','化工','生物','教师','销售']\n",
    ")\n",
    "\n",
    "df2 = pd.DataFrame(\n",
    "    data=np.round(np.random.random(size=(150, 3))*50, 2),  # 计算机科目的考试成绩\n",
    "    columns=['Python','Tensorflow','Keras']\n",
    ")\n",
    "display(df1, df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "a6abd5f7-a659-46c7-9dc3-8a417882c46c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 保存到当前路径下，文件名是salay.xls\n",
    "df1.to_excel(\n",
    "    'salary1.xlsx',         # 保存路径\n",
    "    sheet_name='salary',    # 保存在哪一张表里，工作表的名字\n",
    "    header=True,            # 是否保存列索引\n",
    "    index=False             # 是否保存行索引\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "7e3309e0-4bb3-4960-8ca2-2aa28eac8ba0",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 保存到一个工作簿中的多个工作表\n",
    "with pd.ExcelWriter('data.xlsx') as writer:\n",
    "    df1.to_excel(writer, sheet_name='salary', index=False)\n",
    "    df2.to_excel(writer, sheet_name='score', index=False)\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1add61a0-8e26-41a2-8fe8-3a944270ac8a",
   "metadata": {},
   "source": [
    "## （2）导入"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "7bf2ac53-d7c1-4cdb-a774-375c24191801",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>化工</th>\n",
       "      <th>生物</th>\n",
       "      <th>教师</th>\n",
       "      <th>销售</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IT</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>22</td>\n",
       "      <td>13</td>\n",
       "      <td>43</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>42</td>\n",
       "      <td>40</td>\n",
       "      <td>13</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>38</td>\n",
       "      <td>0</td>\n",
       "      <td>19</td>\n",
       "      <td>46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>11</td>\n",
       "      <td>30</td>\n",
       "      <td>48</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>37</td>\n",
       "      <td>35</td>\n",
       "      <td>40</td>\n",
       "      <td>36</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    化工  生物  教师  销售\n",
       "IT                \n",
       "30  22  13  43  10\n",
       "9   42  40  13  28\n",
       "11  38   0  19  46\n",
       "2   11  30  48  19\n",
       "33  37  35  40  36"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 从Excel文件中读取数据\n",
    "df = pd.read_excel(\n",
    "    'salary1.xlsx',  # 文件路径\n",
    "    sheet_name=0,    # 默认读取第一个表，也就是0\n",
    "    header=0,        # 默认读取第一行作为列索引，也就是0\n",
    "    index_col=0      # 指定某一列作为行索引，默认是None不指定      \n",
    ")\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "57893f1e-d589-46f7-801e-c0d47e27cc7b",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
